﻿
CREATE TABLE [MeterProvider]
(
	[Id] [uniqueidentifier] NOT NULL PRIMARY KEY CLUSTERED,
	[Name] [nvarchar] (32) NOT NULL
)
-- create fk to meter provider

CREATE TABLE [MeterType]
(
	[Id] [char](3) NOT NULL PRIMARY KEY CLUSTERED,
	[Name] [nvarchar](32) NOT NULL,
	[Description] [nvarchar](128) NOT NULL,
	[AggregateType] [nvarchar](8) NOT NULL,
	[UnitOfMeasure] [nvarchar](8) NOT NULL,
	[Weight] [float] NULL DEFAULT 1 --This value contains the overal weight of the metric type compared to other types
)

CREATE TABLE [MeterData]
(
	[MeterTypeId] [char](3) NOT NULL,
	[TenantId] [nvarchar](32) NOT NULL,
	[TimePeriod] [smalldatetime] NOT NULL,
	[Value] [float] NOT NULL,
	[Count] [int] NOT NULL DEFAULT 1,
	
	PRIMARY KEY([MeterTypeId], [TenantId], [TimePeriod]),
	CONSTRAINT [FK_MeterData_MeterType_Id]
    FOREIGN KEY (MeterTypeId) REFERENCES MeterType(Id)
)

CREATE TABLE [Setting]
(
	[Provider] [nvarchar] (128) NOT NULL,
	[Name] [nvarchar] (128) NOT NULL,
	[Value] [ntext],
	PRIMARY KEY([Provider], [Name]),
)

CREATE TABLE [Tenant]
(
	[TenantId] [nvarchar] (64) NOT NULL PRIMARY KEY CLUSTERED,
	[CountryCode] [char] (3) NOT NULL DEFAULT ''
)

CREATE TABLE [ServiceKey]
(
	[Key] [nvarchar] (100) NOT NULL PRIMARY KEY CLUSTERED,
	[UserId] [nvarchar] (100) NOT NULL
)

--TYPE USED FOR BULK UPSERT/MERGE
CREATE TYPE [MeterDataTypeVal] AS TABLE
(
	[MeterTypeId] [char](3),
	[TenantId] [nvarchar](32),
	[TimePeriod] [smalldatetime],
	[Value] [float],
	[Count] [int]
)

GO

-- ------------------------
-- Add Default Meter Types
-- ------------------------

INSERT INTO MeterType ([Id],[Name],[Description],[AggregateType],[UnitOfMeasure], [Weight])
VALUES ('bwi', 'Web Bandwidth Ingress', 'Web Ingress Bandwidth', 'sum', 'kb', 0)

INSERT INTO MeterType ([Id],[Name],[Description],[AggregateType],[UnitOfMeasure], [Weight])
VALUES ('bwe', 'Web Bandwidth Egress', 'Web Egress Bandwidth', 'sum', 'kb', 0.5)

INSERT INTO MeterType ([Id],[Name],[Description],[AggregateType],[UnitOfMeasure], [Weight])
VALUES ('req', 'Requests', 'Web Requests', 'sum', 'count', 0.001)

INSERT INTO MeterType ([Id],[Name],[Description],[AggregateType],[UnitOfMeasure], [Weight])
VALUES ('dbs', 'Database Size', 'Database Size', 'avg', 'kb', 3)

INSERT INTO MeterType ([Id],[Name],[Description],[AggregateType],[UnitOfMeasure], [Weight])
VALUES ('dbi', 'Database Bandwidth Ingress', 'Database Ingress Bandwidth', 'sum', 'kb', 0)

INSERT INTO MeterType ([Id],[Name],[Description],[AggregateType],[UnitOfMeasure], [Weight])
VALUES ('dbe', 'Database Bandwidth Egress', 'Database Egress Bandwidth', 'sum', 'kb', 0)

INSERT INTO MeterType ([Id],[Name],[Description],[AggregateType],[UnitOfMeasure], [Weight])
VALUES ('stg', 'Storage Size', 'Windows Azure Storage Size', 'avg', 'kb', 0.8)

INSERT INTO MeterType ([Id],[Name],[Description],[AggregateType],[UnitOfMeasure], [Weight])
VALUES ('bbw', 'Blob Bandwidth', 'Blob Bandwidth', 'sum', 'kb', 0.01)

-- ADD CUSTOM METRICS WITH WEIGHTS
-- INSERT INTO MeterType ([Id],[Name],[Description],[AggregateType],[UnitOfMeasure], [Weight])
-- VALUES ('etl', 'Project Import', 'Project Extract Transform and Load', 'sum', 'minutes', 0.05)

-- INSERT INTO MeterType ([Id],[Name],[Description],[AggregateType],[UnitOfMeasure], [Weight])
-- VALUES ('job', 'Project Analysis Run', 'Project Analysis Run', 'sum', 'minutes', 0.05)

-- INSERT INTO MeterType ([Id],[Name],[Description],[AggregateType],[UnitOfMeasure], [Weight])
-- VALUES ('mvc', 'Controller Action Count', 'Controller Action Invoked', 'sum', 'count', 0.1)

GO

-- ------------------------
-- Add Default Service Keys
-- ------------------------
INSERT INTO ServiceKey ([Key], [UserId]) VALUES ('cpoj9ewcpccjpewmcewcewo9jmcwepmcewmcw9ejx0', 'test')
GO

-- ------------------------
-- Add Views
-- ------------------------

CREATE VIEW [MonthlyView] AS 
	SELECT  DAT.TenantId ,
			DAT.MeterTypeId ,
			DATEPART(YY, DAT.TimePeriod) AS [Year] ,
			DATEPART(MM, DAT.TimePeriod) AS [Month] ,
			DATEADD(S, -1, DATEADD(MM, (DATEPART(YY, DAT.TimePeriod) - 1900) * 12 + DATEPART(MM, DAT.TimePeriod), 0)) AS TimePeriodEnd,
			CASE TYP.AggregateType
			  WHEN 'sum' THEN ROUND(SUM(DAT.Value),0)
			  WHEN 'avg' THEN ROUND(AVG(DAT.Value / DAT.[Count]),0)
			END AS [Value]
	FROM    dbo.MeterData DAT
			INNER JOIN dbo.MeterType TYP ON DAT.MeterTypeId = TYP.Id
	GROUP BY DAT.TenantId ,
			DAT.MeterTypeId ,
			TYP.AggregateType ,
			DATEPART(YY, DAT.TimePeriod) ,
			DATEPART(MM, DAT.TimePeriod)
GO

CREATE VIEW [dbo].[TotalMonthlyView] AS 
	SELECT  DAT.MeterTypeId ,
			DATEPART(YY, DAT.TimePeriod) AS [Year] ,
			DATEPART(MM, DAT.TimePeriod) AS [Month] ,
			DATEADD(S, -1, DATEADD(MM, (DATEPART(YY, DAT.TimePeriod) - 1900) * 12 + DATEPART(MM, DAT.TimePeriod), 0)) AS TimePeriodEnd,
			ROUND(SUM(DAT.Value),2) AS [Value]
	FROM    dbo.MeterData DAT
			INNER JOIN dbo.MeterType TYP ON DAT.MeterTypeId = TYP.Id
	GROUP BY DAT.MeterTypeId ,
			TYP.AggregateType ,
			DATEPART(YY, DAT.TimePeriod) ,
			DATEPART(MM, DAT.TimePeriod)

GO

CREATE VIEW [TotalDailyView] AS 
	SELECT  DAT.MeterTypeId ,
			DAT.TimePeriod ,
			CASE TYP.AggregateType
			  WHEN 'sum' THEN ROUND(SUM(DAT.Value),2)
			  WHEN 'avg' THEN ROUND(AVG(DAT.Value / DAT.[Count]),2)
			END AS [Value]
	FROM    dbo.MeterData DAT
			INNER JOIN dbo.MeterType TYP ON DAT.MeterTypeId = TYP.Id
	GROUP BY DAT.MeterTypeId ,
			TYP.AggregateType ,
			DAT.TimePeriod
GO

CREATE VIEW [dbo].[TotalResourceUsageView] AS
    SELECT  DAT.TenantId AS TenantId ,
            DATEPART(YY, DAT.TimePeriod) AS [Year] ,
            DATEPART(MM, DAT.TimePeriod) AS [Month] ,
			DATEADD(S, -1, DATEADD(MM, (DATEPART(YY, DAT.TimePeriod) - 1900) * 12 + DATEPART(MM, DAT.TimePeriod), 0)) AS TimePeriodEnd,
            ROUND(SUM(DATDBS.Value / DATDBS.[Count]), 0) AS [TotalDbs] ,
            ROUND(SUM(DATSTG.Value / DATSTG.[Count]), 0) AS [TotalStg] ,
            ROUND(SUM(DATBWE.Value), 0) AS [TotalBwe] ,
            ROUND(
				SUM(DATDBS.Value / DATDBS.[Count]) * (SELECT TOP 1 TYP.Weight FROM dbo.MeterType TYP WHERE Id='dbs')
				+ SUM(DATSTG.Value / DATSTG.[Count]) * (SELECT TOP 1 TYP.Weight FROM dbo.MeterType TYP WHERE Id='stg')
				+ SUM(DATBWE.Value) * (SELECT TOP 1 TYP.Weight FROM dbo.MeterType TYP WHERE Id='bwe')
				, 0) AS TWU
    FROM    dbo.MeterData DAT
            LEFT OUTER JOIN dbo.MeterData DATDBS ON (DAT.TenantId = DATDBS.TenantId) AND (DAT.MeterTypeId = DATDBS.MeterTypeId) AND (DAT.TimePeriod = DATDBS.TimePeriod)
                                                    AND DATDBS.MeterTypeId = 'dbs'
            LEFT OUTER JOIN dbo.MeterData DATSTG ON (DAT.TenantId = DATSTG.TenantId) AND (DAT.MeterTypeId = DATSTG.MeterTypeId) AND (DAT.TimePeriod = DATSTG.TimePeriod)
                                                    AND DATSTG.MeterTypeId = 'stg'
            LEFT OUTER JOIN dbo.MeterData DATBWE ON (DAT.TenantId = DATBWE.TenantId) AND (DAT.MeterTypeId = DATBWE.MeterTypeId) AND (DAT.TimePeriod = DATBWE.TimePeriod)
                                                    AND DATBWE.MeterTypeId = 'bwe'
    GROUP BY DAT.TenantId ,
            DATEPART(YY, DAT.TimePeriod) ,
            DATEPART(MM, DAT.TimePeriod)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE Upsert_MeterData @MeterDataTempTable dbo.[MeterDataTypeVal] READONLY
	AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    MERGE INTO dbo.[MeterData] AS [Target]
                        USING @MeterDataTempTable AS [Source]
                            ON ([Target].MeterTypeId = [Source].MeterTypeId AND [Target].TenantId = [Source].TenantId
                                AND DATEPART(yyyy, [Target].TimePeriod) = DATEPART(yyyy, [Source].TimePeriod)
                                AND DATEPART(mm, [Target].TimePeriod) = DATEPART(mm, [Source].TimePeriod)
                                AND DATEPART(dd, [Target].TimePeriod) = DATEPART(dd, [Source].TimePeriod))
                            WHEN MATCHED THEN
                                    UPDATE SET [Value] = [Target].Value + [Source].Value, [Count] = [Target].[Count] + [Source].[Count]
                            WHEN NOT MATCHED THEN
                                    INSERT ([MeterTypeId], [TenantId], [TimePeriod], [Value], [Count])
                                    VALUES ([Source].MeterTypeId, [Source].TenantId, [Source].TimePeriod, [Source].Value, [Source].[Count]);
END
GO
